package untiring07_HelloJDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class J03_MemberDAO implements J03_IMemberDAO {
	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:untiring07/untiring07@112.216.253.125:1521:xe";
	private String insertSQL = "insert into J03Member values(exam.nextval,?,?,?,?,sysdate)";
	private String selectSQL = " select*from J03Member order by mno desc";
	private String updateSQL = " update J03Member set passwd = ?,name = ?, email=? ,regdate = sysdate where mno = ?";
	private String deleteSQL = " delete J03Member where mno = ?";
	@Override
	public boolean insertMember(J03_MemberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;

		try { 
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(insertSQL);
			pstmt.setString(1, m.getUserid());
			pstmt.setString(2, m.getPasswd());
			pstmt.setString(3, m.getName());
			pstmt.setString(4, m.getEmail());
			pstmt.executeUpdate();
			result = true;

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDatabase(conn, pstmt, null);
		}

		return result;
	} // insertMember

	// JDBC Connection 객체를 생성하는 메서드
	private Connection openDatabase() {
		try {
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		} catch (Exception e) {
			e.printStackTrace();

		}
		return null;
	}

	// JDBC객체를 닫는 메서드
	private void closeDatabase(Connection c, PreparedStatement p, ResultSet r) {
		if (r != null)
			try {
				r.close();
				r = null;
			} catch (Exception e) {
			}
		if (p != null)
			try {
				p.close();
				p = null;
			} catch (Exception e) {
			}
		if (c != null)
			try {
				c.close();
				c = null;
			} catch (Exception e) {
			}
	}

	// select 문으로 전체 회원정보를 조회한 후
	// MemberVO를 내용으로 가지는 동적배열 List로 결과를 넘김

	@Override
	public List<J03_MemberVO> selectMember() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<J03_MemberVO> result = new ArrayList<>();
		try {
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				J03_MemberVO m = new J03_MemberVO();
				m.setMno(rs.getString("mno"));
				m.setUserid(rs.getString("Userid"));
				m.setPasswd(rs.getString("Passwd"));
				m.setName(rs.getString("Name"));
				m.setEmail(rs.getString("Email"));
				m.setRegdate(rs.getString("Regdate"));
				result.add(m);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDatabase(conn, pstmt, null);
		}

		return result;
	}

	@Override
	public boolean updateMember(J03_MemberVO mvo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			conn=this.openDatabase();
			pstmt = conn.prepareStatement(updateSQL);
			pstmt.setString(1,mvo.getPasswd());
			pstmt.setString(2,mvo.getName());
			pstmt.setString(3,mvo.getEmail());
			pstmt.setString(4,mvo.getMno());
			
			int cnt = pstmt.executeUpdate();
			if(cnt == 0 )System.out.println("명령은 잘 실행됐지만, 수정된 데이터는 없음");
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{;
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}

	@Override
	public boolean deleteMember(J03_MemberVO mvo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			conn=this.openDatabase();
			pstmt = conn.prepareStatement(deleteSQL);
			pstmt.setString(1,mvo.getMno());
			pstmt.executeUpdate();
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{;
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
	
	}

}